﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Portal.Exam.Model;
using Portal.Web.Utility;
using Portal.Database.Service;
using Portal.Core;
using Portal.Utility;

namespace Portal.Exam.Bussiness
{
    public class GroupQuestionBll
    {
        static string GetExamConnectionString(string SiteId)
        {
            SiteParam OSiteParam = WebEnvironments.CreateSiteParam(SiteId);
            string ExamConnectionString = WebConfig.GetConfigFromXml(OSiteParam, "ExamConnectionString");
            return ExamConnectionString;
        }


        public static GroupQuestionCls CreateGroupQuestionModel(string SiteId, string GroupQuestionId)
        {
            IDatabaseService DBService = WebDatabaseService.CreateDBService(SiteId, GetExamConnectionString(SiteId));
            DataSet dsResult =
                    DBService.GetDataSet(null, "select * from TableGroupQuestion " +
                                               "     where GroupQuestionId=@GroupQuestionId order by SortIndex",
                    new DbParam[]
                    {
                        new DbParam("GroupQuestionId", GroupQuestionId),
                    });
            if (dsResult.Tables[0].Rows.Count == 0)
            {
                return null;
            }
            GroupQuestionCls OGroupQuestion = GroupQuestionParser.ParseFromDataRow(dsResult.Tables[0].Rows[0]);
            dsResult.Clear();
            dsResult.Dispose();
            return OGroupQuestion;
        }



        public static GroupQuestionCls[] ReadingGroupQuestion(
            string SiteId,
            string ParentGroupQuestionId,
            string AccountType = "")
        {
            IDatabaseService DBService = WebDatabaseService.CreateDBService(SiteId, GetExamConnectionString(SiteId));
            string AssetCode = WebSessionUtility.GetCurrentLoginUser().AssetCode;
            string Query = "select * from TableGroupQuestion where AssetCode='" + AssetCode + "' ";
            if (!string.IsNullOrEmpty(ParentGroupQuestionId))
            {
                Query += " and frkParentGroupQuestionId='" + ParentGroupQuestionId + "'";
            }
            else
            {
                Query += " and frkParentGroupQuestionId is null";
            }

            if (!string.IsNullOrEmpty(AccountType))
            {
                Query += " and AccountType='" + AccountType + "'";
            }

            Query += " order by SortIndex";
            DataSet dsResult = DBService.GetDataSet(null, Query, null);
            if (dsResult.Tables[0].Rows.Count == 0)
            {
                return new GroupQuestionCls[0];
            }
            GroupQuestionCls[] GroupQuestion = GroupQuestionParser.ParseFromDataTable(dsResult.Tables[0]);
            dsResult.Clear();
            dsResult.Dispose();
            return GroupQuestion;
        }



        public static GroupQuestionCls[] ReadingGroupQuestionForAccountType(
            string SiteId,
            string AccountType)
        {
            IDatabaseService DBService = WebDatabaseService.CreateDBService(SiteId, GetExamConnectionString(SiteId));
            string AssetCode = WebSessionUtility.GetCurrentLoginUser().AssetCode;
            string Query = "select * from TableGroupQuestion where AssetCode='" + AssetCode + "' and AccountType='" + AccountType + "' and frkParentGroupQuestionId is null order by SortIndex";
            DataSet dsResult = DBService.GetDataSet(null, Query, null);
            if (dsResult.Tables[0].Rows.Count == 0)
            {
                return new GroupQuestionCls[0];
            }
            GroupQuestionCls[] GroupQuestion = GroupQuestionParser.ParseFromDataTable(dsResult.Tables[0]);
            dsResult.Clear();
            dsResult.Dispose();
            return GroupQuestion;
        }

        //public static GroupQuestionCls[] ReadingGroupQuestionForUser(string SiteId, string GroupDataId, string UserId)
        //{
        //    IDbService DBService = WebEnvironments.CreateDBService(SiteId, GetExamConnectionString(SiteId));
        //    string Query = " select * from TableGroupQuestion where frkGroupDataId='" + GroupDataId + "' and frkParentGroupQuestionId is null "+
        //                   "    and "+
        //                   "    ("+
        //                   "        GroupQuestionId in "+
        //                   "        ("+
        //                   "            select ObjectId from TablePermissionRole"+
        //                   "                where frkPermissionTypeId='DocumentFolder' and type='user' and frkUserId='"+UserId+"' "+
        //                   "        )"+
        //                   "        or"+
        //                   "        GroupQuestionId in"+
        //                   "        ("+
        //                   "            select ObjectId from TablePermissionRole" +
        //                   "                where frkPermissionTypeId='DocumentFolder' and type='role'" +
        //                   "                and frkRoleId in "+
        //                   "                ("+
        //                   "                    select frkRoleId from TableUserRole"+
        //                   "                        where TableUserRole.frkUserId='"+UserId+"'"+
        //                   "                )"+
        //                   "        )"+
        //                   "    )"+
        //                   "  order by SortIndex";
        //    DataSet dsResult = DBService.GetDataSet(null, Query, null);
        //    if (dsResult.Tables[0].Rows.Count == 0)
        //    {
        //        return new GroupQuestionCls[0];
        //    }
        //    GroupQuestionCls[] GroupQuestion = GroupQuestionParser.ParseFromDataTable(dsResult.Tables[0]);
        //    dsResult.Clear();
        //    dsResult.Dispose();
        //    return GroupQuestion;
        //}

        public static string AddGroupQuestion(
            string SiteId,
            string AccountType,
            string GroupQuestionName,
            string Description,
            string RootGroupQuestionId,
            string ParentGroupQuestionId,
            string SortIndex)
        {
            SiteParam OSiteParam = WebEnvironments.CreateSiteParam(SiteId);
            IDatabaseService DBService = WebDatabaseService.CreateDBService(SiteId, GetExamConnectionString(SiteId));
            string GroupQuestionId = System.Guid.NewGuid().ToString();
            string AssetCode = WebSessionUtility.GetCurrentLoginUser().AssetCode;
            string DataLanguage = WebConfig.GetDataLanguage(OSiteParam);
            if (string.IsNullOrEmpty(ParentGroupQuestionId))
            {
                ParentGroupQuestionId = null;
            }
            if (string.IsNullOrEmpty(RootGroupQuestionId))
            {
                RootGroupQuestionId = null;
            }
            DBService.Insert(null, "TableGroupQuestion",
                    new DbParam[]
                    {
                        new DbParam("GroupQuestionId", GroupQuestionId),
                        new DbParam("AssetCode", AssetCode),
                        new DbParam("GroupQuestionName", GroupQuestionName),
                        new DbParam("Description", Description),
                        new DbParam("frkParentGroupQuestionId", ParentGroupQuestionId),
                        new DbParam("RootGroupQuestionId", RootGroupQuestionId),
                        new DbParam("SortIndex", SortIndex),
                        new DbParam("AccountType", AccountType),
                    });
            return GroupQuestionId;
        }


        public static void SaveGroupQuestion(string SiteId, string GroupQuestionId, string GroupQuestionName, string Description, string SortIndex)
        {
            IDatabaseService DBService = WebDatabaseService.CreateDBService(SiteId, GetExamConnectionString(SiteId));
            DBService.Update(null, "TableGroupQuestion", "GroupQuestionId", GroupQuestionId,
                    new DbParam[]
                    {
                        new DbParam("GroupQuestionName", GroupQuestionName),
                        new DbParam("Description", Description),
                        new DbParam("SortIndex", SortIndex),
                    });
        }


        public static void DeleteGroupQuestion(string SiteId, string GroupQuestionId)
        {
            IDatabaseService DBService = WebDatabaseService.CreateDBService(SiteId, GetExamConnectionString(SiteId));
            string Query =
                " Delete from TableGroupQuestion where GroupQuestionId=@GroupQuestionId; ";
            DBService.ExecuteNonQuery(null, Query,
                    new DbParam[]
                    {
                        new DbParam("GroupQuestionId", GroupQuestionId),
                    });
        }



        public static DataTable ReadingQuestionForGroupQuestion(string SiteId, string GroupQuestionId)
        {
            IDatabaseService DBService = WebDatabaseService.CreateDBService(SiteId, GetExamConnectionString(SiteId));
            string Query = " select TableQuestion.* from TableQuestion " +
                           "  where frkGroupQuestionId='" + GroupQuestionId + "'";

            DataTable dtResult = DBService.GetDataTable(null, Query);
            return dtResult;
        }

        public static DataTable ReadingQuestionForGroupQuestion(string SiteId, string GroupQuestionId, string Keyword)
        {
            IDatabaseService DBService = WebDatabaseService.CreateDBService(SiteId, GetExamConnectionString(SiteId));
            string Query = " select TableQuestion.* from TableQuestion " +
                           "    where frkGroupQuestionId='" + GroupQuestionId + "'";
            if (!string.IsNullOrEmpty(Keyword))
            {
                Keyword = FunctionUtilities.CorrectForSearch(Keyword);
                Query += " and Title like N'%" + Keyword + "%'";
            }


            DataTable dtResult = DBService.GetDataTable(null, Query);
            return dtResult;
        }

        public static void RemoveCollectionFromExam(string SiteId, string GroupQuestionId, string QuestionId)
        {
            IDatabaseService DBService = WebDatabaseService.CreateDBService(SiteId, GetExamConnectionString(SiteId));
            string Query = " Delete from TableGroupQuestion " +
                           "    where frkGroupQuestionId='" + GroupQuestionId + "' and frkExamQuestionId='" + QuestionId + "'";

            DBService.ExecuteNonQuery(null, Query, null);
        }

        public static DataTable ReadingQuestionForGroupQuestionWithExamTemp(
           string SiteId,
           string GroupQuestionId,
           string Keyword,
           string ExamTemplateId,
           string SelectLevel)
        {
            IDatabaseService DBService = WebDatabaseService.CreateDBService(SiteId, GetExamConnectionString(SiteId));
            //string Query = " select TableQuestion.* from TableQuestion " +
            //               "    where frkGroupQuestionId='" + GroupQuestionId + "'";


            string Query = " select QuestionId,Title,MaxPoint,Type,GroupQuestionName=TableGroupQuestion.GroupQuestionName,RootGroupQuestionName=TableGroupQuestionRoot.GroupQuestionName from TableQuestion " +
                           "           left outer join TableGroupQuestion on TableGroupQuestion.GroupQuestionId=frkGroupQuestionId" +
                           "           left outer join TableGroupQuestion as TableGroupQuestionRoot on TableGroupQuestionRoot.GroupQuestionId=TableGroupQuestion.RootGroupQuestionId" +
                //               "    where TableGroupQuestion.RootGroupQuestionId='" + GroupQuestionId + "' ";
            "    where frkGroupQuestionId='" + GroupQuestionId + "' and Level='" + int.Parse(SelectLevel) + "'";
            if (!string.IsNullOrEmpty(Keyword))
            {
                Query += " and Title like N'%" + Keyword + "%'";
            }
            if (!string.IsNullOrEmpty(ExamTemplateId))
            {
                Query += " and QuestionId not in (select frkQuestionId from TableExamTemplateQuestion where frkTemplateId='" + ExamTemplateId + "')";
            }

            DataTable dtResult = DBService.GetDataTable(null, Query);
            return dtResult;
        }

        public static DataTable ReadingQuestionForGroupQuestionByExamTempId(
           string SiteId,
           string ExamTemplateId
           )
        {
            IDatabaseService DBService = WebDatabaseService.CreateDBService(SiteId, GetExamConnectionString(SiteId));

            string Query = " select QuestionId,Title,MaxPoint,Level,Type,GroupQuestionName=TableGroupQuestion.GroupQuestionName,RootGroupQuestionName=TableGroupQuestionRoot.GroupQuestionName from TableQuestion " +
                            "           left outer join TableGroupQuestion on TableGroupQuestion.GroupQuestionId=frkGroupQuestionId" +
                            "           left outer join TableGroupQuestion as TableGroupQuestionRoot on TableGroupQuestionRoot.GroupQuestionId=TableGroupQuestion.RootGroupQuestionId" +
                            "    where QuestionId in (select frkQuestionId from TableExamTemplateQuestion where frkTemplateId='" + ExamTemplateId + "')";

            DataTable dtResult = DBService.GetDataTable(null, Query);
            return dtResult;
        }
    }
}
